package db;

import beans.MessageBean;
import beans.ForumBean;
import beans.UserBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletContext;

/**
 * Class for show forum and add message to it.
 * @author Tomas CASTA
 */
public class Forum {
    /** list of all forumList in forum */
    private ArrayList<MessageBean> list;
    /** info about forum */
    private ForumBean forum;
    /** connection to db */
    private Connection connection;
    /** message for errors */
    private String msg;
    /** servlet context */
    private ServletContext context;
    /** statement for queries */
    private PreparedStatement stmt;
    /** set for results */
    private ResultSet resultSet;
    /** how many forum to one page */
    private static final int PAGGING = 5;
    /** count of all pages */
    private int countOfPages;
    /** current page in pagging */
    private int currentPage;
    /** list of all forums */
    private ArrayList<ForumBean> forumList;

    /**
     * Constructor.
     */
    public Forum() {
        this.list = new ArrayList<MessageBean>();
        this.forumList = new ArrayList<ForumBean>();
    }

    public ArrayList<ForumBean> getForumList() {
        return forumList;
    }

    public void setForumList(ArrayList<ForumBean> forumList) {
        this.forumList = forumList;
    }

    public int getCountOfPages() {
        return countOfPages;
    }

   

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCountOfPages(int countOfPages) {
        this.countOfPages = countOfPages;
    }

    
    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public ArrayList<MessageBean> getList() {
        return list;
    }

    public void setList(ArrayList<MessageBean> list) {
        this.list = list;
    }

    public ForumBean getMessages() {
        return forum;
    }

    public void setMessages(ForumBean forum) {
        this.forum = forum;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public ForumBean getForum() {
        return forum;
    }

    public void setForum(ForumBean forum) {
        this.forum = forum;
    }


    
    

    /**
     * Set connection to db.
     * @param context ServletContext  context of actual servler
     * @return boolean true ok, false error
     */
    public boolean setDb(ServletContext context) {
        this.context = context;
        Db db = new Db();

        /** Get params from web.xml */
        db.setDb(context.getInitParameter("db-machine"), context.getInitParameter("db-db"), context.getInitParameter("db-user"), context.getInitParameter("db-pass"));
        this.connection = db.connectToDB();
        msg = db.getMsg();
        if (this.connection == null) {
            return false;
        } else {
            return true;
        }
    }

    /**
     * Add new mesage to forum given by id.
     * @param id int id forum for adding new mesages
     * @param msg String text of message
     * @param user UserBean info about author of message
     * @return boolean true ok, false error
     */
    public boolean addMessageToForum(int id, String msg, UserBean user) {
        try {

            /* Add new message */
            stmt = this.connection.prepareStatement("INSERT INTO casta_message (id, id_forum, id_user, posted_at, text) VALUES (NULL, ?, ?, NULL, ?)");
            stmt.setInt(1, id);
            stmt.setInt(2, user.getId());
            //String msgBr = msg.replace(System.getProperty("line.separator"), "<br />");
            /* Add <pre> tag because of linefeed */
            stmt.setString(3, msg);

            /* Error in query */
            if (stmt.executeUpdate() != 1) {
                stmt.close();
                connection.close();
                return false;
            }

            stmt.close();
            connection.close();
            return true;

        } catch (SQLException ex) {
            Logger.getLogger(Forum.class.getName()).log(Level.SEVERE, null, ex);
            return false;

        }
        catch (NullPointerException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
            msg = "Databáze není přístupná.";
            return false;
        }

        
    }
    /**
     * Show forum by given id.
     * @param id int forum id
     * @return boolean true ok, false error
     */
    public boolean showForumById(int id) {
        try {

            /* Select all messages forum forum by given id */
            stmt = this.connection.prepareStatement("SELECT * FROM casta_message WHERE casta_message.id_forum = ?");
            stmt.setInt(1, id);
            resultSet = stmt.executeQuery();

            /* For each message in forum get info about user */
            while (resultSet.next()) {

                /* Info about user */
                User user = new User();
                user.setDb(context);
                user.findById(resultSet.getInt("id_user"));

                /* Add info to list */
                list.add(new MessageBean(resultSet.getInt("id"), resultSet.getInt("id_forum"), resultSet.getInt("id_user"), resultSet.getString("posted_at"), resultSet.getString("text"), user.getUserBean()));
            }

            stmt = this.connection.prepareStatement("SELECT * FROM casta_forum WHERE id = ?");
            stmt.setInt(1, id);
            resultSet = stmt.executeQuery();

            /* Get forum info */
            if (resultSet.next()) {
                forum = new ForumBean(resultSet.getInt("id"), resultSet.getString("name"), 0, null, null);
            }

            stmt.close();
            connection.close();
            return true;

        } catch (SQLException ex) {

            Logger.getLogger(ForumBean.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

        catch (NullPointerException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
            msg = "Databáze není přístupná.";
            return false;
        }
    }

     /**
      * Get count of all forum pages, set it to attribute.
      * @return booean true ok, false error
      */
     private boolean getCountOfAllPages() {
        try {
            stmt = this.connection.prepareStatement("SELECT * FROM casta_forum");
            resultSet = stmt.executeQuery();

            /* Move to last row */
            resultSet.last();
            
            /* Get 1 if is there one more page */
            int rest = resultSet.getRow() % PAGGING;

            /* Get count of forums per page */
            countOfPages = resultSet.getRow() / PAGGING;

            /* Increase */
            if (rest > 0) {
                countOfPages++;
            }
            stmt.close();
            return true;

        } catch (SQLException ex) {
            Logger.getLogger(Forum.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

     /**
      * Delete forum by given id.
      * @param id int id of forum to delete
      * @return boolean true ok, false error
      */
    public boolean deleteForum(int id) {
        try {

            /* Delete forum */
            stmt = this.connection.prepareStatement("DELETE FROM casta_forum WHERE id = ?");
            stmt.setInt(1, id);
            
            /* Error in query */
            if (stmt.executeUpdate() != 1) {
                stmt.close();
                connection.close();
                return false;
            }

            /* Delete all messages from this forum */
            stmt = this.connection.prepareStatement("DELETE FROM casta_message WHERE id_forum = ?");
            stmt.setInt(1, id);

            /* Error in query */
            if (stmt.executeUpdate() != 1) {
                stmt.close();
                connection.close();
                return false;
            }

            stmt.close();
            connection.close();
            return true;

        } catch (SQLException ex) {
            Logger.getLogger(Forum.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

        catch (NullPointerException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
            msg = "Databáze není přístupná.";
            return false;
        }


    }

    /**
     * Add new forum.
     * @param name String name of new forum
     * @param msg String text of first message in this forum
     * @param user UserBean author of forum
     * @return int 1 ok, -1 another forum with this name already exists, -3, -4 SQL error
     */
    public int addForum (String name, String msg, UserBean user) {
        try {
            stmt = connection.prepareStatement("SELECT id FROM casta_forum WHERE name = ?");
            stmt.setString(1, name);
            ResultSet rs = stmt.executeQuery();

            /* Forum with this name already exists */
            if (rs.next()) {
                stmt.close();
                connection.close();
                this.msg = "Fórum s daným názvem " + name + " již existuje.";
                return -1;
            }


            /* Add new forum */
            stmt = this.connection.prepareStatement("INSERT casta_forum (id, name) VALUES (NULL, ?)");
            stmt.setString(1, name);
            if (stmt.executeUpdate() != 1) {
                stmt.close();
                connection.close();
                return -4;
            }

            /* Get new forum id */
            stmt = this.connection.prepareStatement("SELECT id from casta_forum WHERE name = ?");
            stmt.setString(1, name);
            rs = stmt.executeQuery();


            /* Add mesage to new forum */
            if (rs.next()) {
                int idForum = rs.getInt("id");
                Forum forum = new Forum();
                forum.setDb(context);
                forum.addMessageToForum(idForum, msg, user);
                stmt.close();
                connection.close();
                return 1;
            } else {
                stmt.close();
                connection.close();
                return -3;
            }



        } catch (SQLException ex) {
            Logger.getLogger(Forum.class.getName()).log(Level.SEVERE, null, ex);
            return -5;
        }

        catch (NullPointerException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
            msg = "Databáze není přístupná.";
            return -6;
        }

    }

    /**
     * Show forums to one page by given page numbers.
     * @param from int from which page start
     * @return boolean true ok, false error
     */
    public boolean showForum(int from) {
        try {

            /* We start from 1, but in db from 0, this correct it */
            from--;
            
            /* Next currrent page will be */
            this.currentPage = from + 1;
            this.getCountOfAllPages();

            /* Get forum for actual page */
            stmt = this.connection.prepareStatement("SELECT * FROM casta_forum ORDER BY id DESC LIMIT ?, ? ");
            stmt.setInt(1, (from * PAGGING));
            stmt.setInt(2, PAGGING);
            resultSet = stmt.executeQuery();


            /* Get info about all forums on current page */
            while (resultSet.next()) {

                /* Get count of mesages in one forum*/
                stmt = this.connection.prepareStatement("SELECT COUNT(*) FROM casta_message WHERE casta_message.id_forum = ?");
                stmt.setInt(1, resultSet.getInt(1));
                ResultSet rsCount = stmt.executeQuery();


                /* Get last message in forum */
                stmt = this.connection.prepareStatement("SELECT * FROM casta_message WHERE casta_message.id_forum = ? ORDER BY posted_at DESC");
                stmt.setInt(1, resultSet.getInt(1));
                ResultSet rsLast = stmt.executeQuery();


                /* Add info to list */
                if (rsLast.next() && rsCount.next()) {

                    /* Find info about user by id */
                    User user = new User();
                    user.setDb(context);
                    user.findById(rsLast.getInt("id_user"));
                    String login = user.getUserBean().getLogin();

                    forumList.add(new ForumBean(resultSet.getInt("id"), resultSet.getString("name"), rsCount.getInt(1),
                            new MessageBean(rsLast.getInt("id"), rsLast.getInt("id_forum"), rsLast.getInt("id_user"), rsLast.getString("posted_at"), rsLast.getString("text")), login));
                }
            }

            stmt.close();
            connection.close();
            return true;

        } catch (SQLException ex) {
            Logger.getLogger(Forum.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

        catch (NullPointerException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
            msg = "Databáze není přístupná.";
            return false;
        }

    }
}
